package org.xtb.CS_3;

import org.xtb.CS_3.AppServerHandler;
import org.xtb.pojo.Contacts;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.net.ServerSocket;
import java.net.Socket;
import java.sql.*;
import java.util.List;


public class DatabaseHandler extends Thread {
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/address_book";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";
    private static final String END_FLAG = "###END###";
    private Socket appSocket;
    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    private BufferedReader in;
    private PrintWriter out;

    public DatabaseHandler(Socket appSocket) {
        this.appSocket = appSocket;
    }

    public void run() {
        try {
            in = new BufferedReader(new InputStreamReader(appSocket.getInputStream()));
            out = new PrintWriter(appSocket.getOutputStream(), true);

            String inputLine;
            while ((inputLine = in.readLine()) != null) {
                System.out.println("应用服务器请求：" + inputLine);

                // 解析请求类型和数据
                String[] request = inputLine.split(":", 2);
                String requestType = request[0].trim();
                String requestData =null;
                if (request.length>1) requestData=request[1].trim();

                Statement statement = getConnection().createStatement();

                // 根据请求类型调用相应的方法
                switch (requestType) {
                    case "创建联系人表":
                        createContactTable();
                        break;
                    case "添加联系人":
                        addContact(requestData);
                        break;
                    case "修改联系人":
                        modifyContact(requestData);
                        break;
                    case "删除联系人":
                        deleteContact(requestData);
                        break;
                    case "查看联系人":
                        String response = getContactList();
                        sendResponse(response);
                        break;
                    default:
                        sendResponse("无效的请求");
                        break;
                }
            }

            in.close();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException | ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    private void createContactTable() {
        try (Connection connection = getConnection(); Statement statement = connection.createStatement()) {
            statement.executeUpdate("CREATE TABLE IF NOT EXISTS contacts (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, address VARCHAR(255), phone VARCHAR(20))");
            sendResponse("联系人表创建成功");
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
            sendResponse("联系人表创建失败");
        }
    }

    private void addContact(String contactData) {
        try (Connection connection = getConnection(); PreparedStatement statement = connection.prepareStatement("INSERT INTO contacts (name, address, phone) VALUES (?, ?, ?)")) {
            String[] contactInfo = contactData.split(",");
            if (contactInfo.length != 3) {
                sendResponse("添加联系人失败：数据格式不正确");
                return;
            }
            String name = contactInfo[0].trim();
            String address = contactInfo[1].trim();
            String phone = contactInfo[2].trim();

            statement.setString(1, name);
            statement.setString(2, address);
            statement.setString(3, phone);
            statement.executeUpdate();
            sendResponse("添加联系人成功");
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
            sendResponse("添加联系人失败");
        }
    }

    private void modifyContact(String contactData) {
        try (Connection connection = getConnection();
             PreparedStatement statement = connection.prepareStatement("UPDATE contacts SET name = ?, address = ?, phone = ? WHERE id = ?")) {
            String[] contactInfo = contactData.split(",");
            if (contactInfo.length != 4) {
                sendResponse("修改联系人失败：数据格式不正确");
                return;
            }
            int id = Integer.parseInt(contactInfo[0].trim());
            String name = contactInfo[1].trim();
            String address = contactInfo[2].trim();
            String phone = contactInfo[3].trim();

            statement.setString(1, name);
            statement.setString(2, address);
            statement.setString(3, phone);
            statement.setInt(4, id);
            int rowsAffected = statement.executeUpdate();

            if (rowsAffected > 0) {
                sendResponse("修改联系人成功");
            } else {
                sendResponse("联系人不存在");
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
            sendResponse("修改联系人失败");
        }
    }

    private void deleteContact(String idStr) {
        try (Connection connection = getConnection();
             PreparedStatement statement = connection.prepareStatement("DELETE FROM contacts WHERE id = ?")) {
            int id = Integer.parseInt(idStr.trim());
            statement.setInt(1, id);
            int rowsAffected = statement.executeUpdate(); if (rowsAffected > 0) {
                sendResponse("删除联系人成功");
            } else {
                sendResponse("联系人不存在");
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
            sendResponse("删除联系人失败");
        }
    }

    private String getContactList() {
        try (Connection connection = getConnection(); Statement statement = connection.createStatement()) {
            ResultSet resultSet = statement.executeQuery("SELECT * FROM contacts");

            return getListHead(resultSet);

        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
            return "获取联系人列表失败";
        }
    }

    private String getListHead(ResultSet resultSet) throws SQLException {
        if (resultSet==null) {
            return "通讯录为空";
        } else {
            StringBuilder sb = new StringBuilder();
            sb.append("联系人列表：\n");
            sb.append("===============================================\n");
            sb.append("id\t\t姓名\t\t住址\t\t电话\n");
            sb.append("===============================================\n");
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String address = resultSet.getString("address");
                String phone = resultSet.getString("phone");
                sb.append(id).append("\t\t").append(name).append("\t\t").append(address).append("\t\t").append(phone).append("\n");
            }
            sb.append("===============================================\n");
            return sb.toString();
        }
    }

    private void sendResponse(String response) {
        out.println(response);
        out.println(END_FLAG);
    }


    private Connection getConnection() throws SQLException, ClassNotFoundException {
        if (connection == null || connection.isClosed()) {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
        }
        return connection;
    }

    private void closeConnection() throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }

    private void executeUpdate(Statement statement, String query) throws SQLException {
        statement.executeUpdate(query);
    }

    private String executeQuery(Statement statement, String query) throws SQLException {
        StringBuilder sb = new StringBuilder();
        resultSet = statement.executeQuery(query);

        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (resultSet.next()) {
            for (int i = 1; i <= columnCount; i++) {
                sb.append(metaData.getColumnName(i)).append(": ")
                        .append(resultSet.getString(i)).append(", ");
            }
            sb.append("\n");
        }

        return sb.toString();
    }



    public static void main(String[] args) {
        try (ServerSocket serverSocket = new ServerSocket(8889)) {
            System.out.println("应用服务器已启动，等待客户端连接...");

            while (true) {
                Socket clientSocket = serverSocket.accept();
                System.out.println("客户端已连接");

                AppServerHandler appServerHandler = new AppServerHandler(clientSocket);
                appServerHandler.start();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}